library(tidyverse)
if (!"purrr" %in% installed.packages()) install.packages("purrr")
source("https://raw.githubusercontent.com/zoerehnberg/STAT331-S23/main/challenges/bCH_murder_setup.R")Bonus Challenge: Murder in SQL City
Joins and Summaries
For this challenge, you will be using table joins to solve a murder mystery.
Guilty Party(ies):
Instructions
Northwestern University’s Knight Lab wanted to help sharpen users’ database skills, so they created a murder mystery. Can you solve this crime in SQL City??
The database you will be working with contains tables with different pieces of information pertinent to the crime - people, social media check-ins, driver’s licenses, crime scene reports, police interviews, and more.
<<<<<<< HEAD
Solve the murder mystery, showing all of your work in this document. For full credit, your document must be well organized and easy to follow. Use headers, good code formatting practices, and comment your code. You MUST include any external references you use to solve this mystery.
Your solution must be reproducible – that is, you should use dplyr verbs, table joins, etc. to solve the mystery, rather than just looking through the tables yourself. Your code must execute properly and stand alone.
When you are finished solving the mystery:
- Write the name of the guilty party or parties at the top of the assignment.
- Turn your rendered HTML document in to Canvas.
Solve the Crime (+10)
This code chunk will read in all of the tables for you.
Tip
In my solution, I used str_detect() a couple of times. This function is from the stringr package (part of the tidyverse!) which we will cover in week 5 of the course.
To get you started, I’ll set you up in the right place.
A crime has taken place and the detective needs your help. The detective gave you the crime scene report, but you somehow lost it. You vaguely remember that the crime was a murder that occurred sometime on Jan.15, 2018 and that it took place in SQL City. Start by retrieving the corresponding crime scene report from the police department’s database.